<?php

use \Phalcon\Db\Column as Column;


class MysqlController extends \Phalcon\Mvc\Controller
{

    const LOOP_CNT = 1;

    public function indexAction() {
        echo 'this is index.<br>';
        echo '<pre>';
        print_r($this->request->get());
        echo '</pre>';
 
    }
    
    public function queryAction() {

        $labels = $this->mytotal1->fetchAll(
                 "SELECT * FROM label", Phalcon\Db::FETCH_ASSOC);

        echo 'label：'. count($labels) . '件<br>';
        foreach($labels as $label) {
            $label['cat'] = unserialize($label['cat']);
            echo '<pre>';
            print_r($label);
	     //print_r(get_object_vars($doc));
            echo '</pre>';
        }


        $conds = array();
         
        $params = $this->request->get();
        foreach($params as $key=>$value) {
            if ($key != '_url') {
                $conds[$key] = $value;
             }
        }
       
        $conditions = implode(' and ', $conds);
         // multi conditions is AND
        
///TODO

        echo 'rawdata：'. count($res) . '/' . Raw::count() . '件<br>';

        foreach($res as $doc) {
            echo '<pre>';
            //print_r($doc);
	     print_r(get_object_vars($doc));
            echo '</pre>';
        }


    }

    public function clearAction() {

        $request = $this->request;
        $id = $request->hasQuery('id') ? $request->get('id') : null;

        if (empty($id)) {
            $newdata = $this->mytotal2->fetchOne(
                 "SELECT * FROM data ORDER BY id DESC", Phalcon\Db::FETCH_ASSOC);

            $id = $newdata['id'];

        }
 
       $this->mytotal2->dropTable('label_' . $id);
       $this->mytotal2->dropTable('rawdata_' . $id);
 
    }

    public function importsAction() {
 
         // todo
        $filel = __DIR__ . '/label.csv';
        $filer = __DIR__ . '/rawdata_s.csv';

        $this->importLabel($filel);
        $this->importRawdata($filer);

   }

    public function importmAction() {
 
         // todo
        $filel = __DIR__ . '/label_m.csv';
        $filer = __DIR__ . '/rawdata_m.csv';

        $this->importLabel($filel);
        $this->importRawdata($filer);

   }

    public function importlAction() {
 
set_time_limit(60);

         // todo
        $filel = __DIR__ . '/label_l.csv';
        $filer = __DIR__ . '/rawdata_l.csv';

        $this->importLabel($filel);
        $this->importRawdata($filer);

   }

    public function importAction() {
 
ini_set('memory_limit', '-1');

         // todo
        $filel = __DIR__ . '/label.csv';
        $filer = __DIR__ . '/rawdata.csv';

        $this->importLabel($filel);
        $this->importRawdata($filer);

   }

    private function createTable() {

        if (! $this->mytotal1->tableExists('data','total')) {
        $this->mytotal1->createTable('data','total',array(
        "columns" => array(
            new Column("id",
                array(
                    "type"          => Column::TYPE_INTEGER,
                    "size"          => 10,
                    "notNull"       => true,
                    "autoIncrement" => true,
                    "primary"       => true,
                )
            ),
            new Column("name",
                array(
                    "type"    => Column::TYPE_VARCHAR,
                    "size"    => 255,
                    "notNull" => true,
                )
            ),
 
        ))
        );
        }

        if (! $this->mytotal1->tableExists('label','total')) {
        $this->mytotal1->createTable('label','total',array(

        "columns" => array(
/*            new Column("id",
                array(
                    "type"          => Column::TYPE_INTEGER,
                    "size"          => 10,
                    "notNull"       => true,
                )
            ),
*/
            new Column("cno",
                array(
                    "type"    => Column::TYPE_INTEGER,
                    "size"    => 10,
                    "notNull" => true,
                  )
              ),
            new Column("cno2",
                array(
                    "type"    => Column::TYPE_INTEGER,
                    "size"    => 10,
                )
            ),
	    new Column("la",
		array(
		    "type"    => Column::TYPE_VARCHAR,
		    "size"    => 255,
                )
            ),
	    new Column("cd",
		array(
		    "type"    => Column::TYPE_VARCHAR,
		    "size"    => 30,
                )
            ),
	    new Column("cd2",
		array(
		    "type"    => Column::TYPE_VARCHAR,
		    "size"    => 30,
                )
            ),
	    new Column("t",
		array(
		    "type"    => Column::TYPE_VARCHAR,
		    "size"    => 2,
                )
            ),
	    new Column("nm",
		array(
		    "type"    => Column::TYPE_VARCHAR,
		    "size"    => 1000,
                )
            ),
	    new Column("nm2",
		array(
		    "type"    => Column::TYPE_VARCHAR,
		    "size"    => 255,
                )
            ),
	    new Column("cat",
		array(
		    "type"    => Column::TYPE_TEXT,
                )
           ),
        ),

        ));
        }


    }


    private function importLabel($file) {

        $this->createTable();

        $data = file_get_contents($file);
        $data = mb_convert_encoding($data, 'UTF-8', 'sjis');
        $temp = tmpfile();
        fwrite($temp, $data);

    for ($n=1;$n<=self::LOOP_CNT;$n++) {

        rewind($temp);

        $this->mytotal1->insert(
            "data",
            array("test data" . $n),
            array("name",)
         );
        $newid = $this->mytotal1->lastInsertId();
        $tblname = 'label_' . $newid;

        $this->mytotal1->execute('create table ' . $tblname . ' like label;');
 
        while (($row = fgetcsv($temp, 0, ",")) !== FALSE) {

            if (!is_numeric($row[0]))
                continue;

            $label = array();

            //id
//            $label[] = $newid;
            //cno
            $label[] = (int)$row[0];
            //cno2
            $label[] = empty($row[1]) ? null : $row[1];
            //la
            $label[] = $row[2];
            //cd
            $label[] = empty($row[3]) ? null : $row[3];
            //nm
            $label[] = empty($row[4]) ? null : $row[4];
            //nm2
            $label[] = empty($row[5]) ? null : $row[5];
            //t
            $label[] = empty($row[6]) ? null : $row[6];
              
            $cat = array();
            $i = 8;
            while(!empty($row[$i])) {
                $cat[] = array('no'=>$i-7, 'nm'=>$row[$i]);
                $i++;
              }
            //cat
            $label[] = serialize($cat);

// echo '<pre>';
// print_r($label);
// echo '<pre>';


            $this->mytotal1->insert(
                 $tblname,
                 $label,
                 array("cno", "cno2", "la", "cd", "nm", "nm2", "t", "cat")
            );
        }
    }
        fclose($temp);

        echo 'label is imported.<br>';


    }


    private function importRawdata($file) {

        $data = file_get_contents($file);
        $data = mb_convert_encoding($data, 'UTF-8', 'sjis');
        $temp = tmpfile();
        fwrite($temp, $data);

        rewind($temp);
        $head = fgetcsv($temp, 0, ",");

        $newdata = $this->mytotal1->fetchOne(
                 "SELECT * FROM data ORDER BY id DESC", Phalcon\Db::FETCH_ASSOC);

        $dataid = $newdata['id'];

        $labels = $this->mytotal1->fetchAll(
                 "SELECT * FROM label_" . $dataid , Phalcon\Db::FETCH_ASSOC);

/*        echo '<pre>';
        print_r($labels);
        echo '</pre>';

        echo 'カラム名<pre>';
        print_r($head);
        echo '<pre>';
*/

        $columns = array();

        foreach($labels as $label) {
            
//            echo '<pre>';
//            print_r($label);
//            echo '</pre>';

            if ($label['t'] == 'MA') {

                for($m = $label['cno']; $m <= $label['cno2']; $m++) {

                    $columns[] = new Column($head[$m-1],
                        array(
                           "type"          => Column::TYPE_INTEGER,
//                           "size"          => 2,
                      ));

                   }


            } elseif ($label['t'] == 'FA') {
                $columns[] = new Column($label['la'],
                    array(
                    "type"          => Column::TYPE_VARCHAR,
                    "size"          => 2000,
                  ));
            
             } elseif ($label['t'] == 'NU') {
                $columns[] = new Column($label['la'],
                    array(
                    "type"          => Column::TYPE_DECIMAL,
//                    "size"          => 10,
                  ));

             } elseif ($label['t'] == 'SA') {
                $columns[] = new Column($label['la'],
                    array(
                    "type"          => Column::TYPE_INTEGER,
//                    "size"          => 2,
                  ));
 
             } elseif($label['cno'] == 1) {
                $columns[] = new Column($label['la'],
                    array(
                    "type"          => Column::TYPE_INTEGER,
//                    "size"          => 2,
                  ));

               }


        }

/*        echo '<pre>';
        print_r($columns);
        echo '</pre>';
*/
        $tablenm = 'rawdata_' . $dataid;
        if (! $this->mytotal1->tableExists($tablenm,'total')) {
            $this->mytotal1->createTable($tablenm,'total', array("columns" => $columns));
        }

        echo "$tablenm is created.<br>";

/*
        rewind($temp);
 
        $rowno = 1;
        $colsize = count($head);

        while (($row = fgetcsv($temp, 0, ",")) !== FALSE) {

            $this->mytotal1->insert($tablenm, $row, $head);


         }
*/

        $metaDatas = stream_get_meta_data($temp);
        $tmpFilename = $metaDatas['uri'];
        $loadcmd = "LOAD DATA LOCAL INFILE '" . $tmpFilename . "' INTO TABLE $tablenm FIELDS TERMINATED BY ',' IGNORE 1 LINES;";
        $this->mytotal1->execute($loadcmd);

        echo "$loadcmd <br>";

        fclose($temp);

        echo 'rawdata is imported.<br>';
    }


}         


